/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bumblebee.rdis;

import com.bumblebee.entities.Booking;
import com.bumblebee.entities.Customer;
import com.bumblebee.entities.Item;
import com.bumblebee.entities.Location;
import com.bumblebee.entities.Room;
import com.bumblebee.entities.Transaction;
import com.bumblebee.entities.User;
import com.bumblebee.sessions.BookingFacadeLocal;
import com.bumblebee.sessions.CustomerFacadeLocal;
import com.bumblebee.sessions.ItemFacadeLocal;
import com.bumblebee.sessions.LocationFacadeLocal;
import com.bumblebee.sessions.RoomFacadeLocal;
import com.bumblebee.sessions.TransactionFacadeLocal;
import com.bumblebee.sessions.UserFacadeLocal;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.*;
import javax.ejb.EJB;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JasperCompileManager;
import net.sf.jasperreports.engine.JasperExportManager;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.design.JasperDesign;
import net.sf.jasperreports.engine.export.JRPdfExporter;
import net.sf.jasperreports.engine.xml.JRXmlLoader;

/**
 *
 * @author numessi <namdq.nu@gmail.com>
 */
@WebServlet(name = "controller", urlPatterns = "/controller")
public class controller extends HttpServlet {

    @EJB
    private BookingFacadeLocal BookingEJB;
    @EJB
    private CustomerFacadeLocal CustomerEJB;
    @EJB
    private ItemFacadeLocal ItemEJB;
    @EJB
    private LocationFacadeLocal LocationEJB;
    @EJB
    private RoomFacadeLocal RoomEJB;
    @EJB
    private TransactionFacadeLocal TransactionEJB;
    @EJB
    private UserFacadeLocal UserEJB;

    /**
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            String action = request.getParameter("Action");
            if (request.getAttribute("Action") != null) {
                action = (String) request.getAttribute("Action");
            }
            if (action.equals("book_rooms")) {
                bookRooms(request, response);
            } else if (action.equals("get_available_rooms")) {
                getAvailableRooms(request, response);
            } else if (action.equals("get_all_rooms")) {
                getAllRooms(request, response);
            } else if (action.equals("get_customer")) {
                getCustomer(request, response);
            } else if (action.equals("get_booked_rooms")) {
                getBookedRoom(request, response);
            } else if (action.equals("login")) {
                doLogin(request, response);
            } else if (action.equals("logout")) {
                doLogout(request, response);
            } else if (action.equals("delete_book_room")) {
                deleteBookedRoom(request, response);
            } else if (action.equals("get_all_items")) {
                getAllItems(request, response);
            } else if (action.equals("sell_items")) {
                sellItems(request, response);
            } else if (action.equals("get_transactions")) {
                getTransactions(request, response);
            } else if (action.equals("delete_transaction")) {
                deleteTransaction(request, response);
            } else if (action.equals("create_local_booking_report")) {
                createBookingReport(request, response);
            } else if (action.equals("create_local_sales_report")) {
                createSalesReport(request, response);
            } else if (action.equals("create_local_user_statistics")) {
                createUserStatistics(request, response);
            }
        } catch (Exception ex) {
            Logger.getLogger(controller.class.getName()).log(Level.SEVERE, null, ex);
        }
        HttpSession session = request.getSession();
        session.setAttribute("Action", null);
        request.setAttribute("Action", null);
    }

    protected void doLogin(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        String userName = req.getParameter("username");
        String userPassword = req.getParameter("password");
        HttpSession session = req.getSession();
        try {
            User user = UserEJB.login(userName, userPassword);
            if (user != null) {
                session.setAttribute("Status", "Successful");
                session.setAttribute("User", user);
                session.setAttribute("UserId", user.getUserId());
                session.setAttribute("FullName", user.getUserFullname());
                session.setAttribute("UserRole", user.getUserRole());
                if (!user.getUserRole().equals("NationalManager")) {
                    session.setAttribute("UserLocationID", user.getUserLocationId());
                }
            } else {
                session.setAttribute("Status", "Incorrect Username or Password");
            }
        } catch (Exception e) {
            System.out.print(e);
        }
        session.setAttribute("Action", null);

    }

    protected void doLogout(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        req.getSession().removeAttribute("User");
        res.sendRedirect("login.jsp");
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        processRequest(req, res);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        processRequest(req, res);
    }

    /**
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

    private void getAvailableRooms(HttpServletRequest request, HttpServletResponse response) throws IOException, ParseException {
        HttpSession session = request.getSession();
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String result = null;
        try {
            String startDate = request.getParameter("ArrivalDate");
            String endDate = request.getParameter("DepartureDate");
            if (startDate == null || endDate == null) {
                result = "Sorry. The dates are invalid";
            }
            Date startSQLDate = Date.valueOf(startDate);
            Date endSQLDate = Date.valueOf(endDate);

            System.out.println(startSQLDate + " ------ " + endSQLDate);
            List<Room> listAvailableRoom = new ArrayList<Room>();
            if (((String) session.getAttribute("UserRole")).equals("NationalManager")) {
                listAvailableRoom = RoomEJB.getAvailableRooms(startSQLDate, endSQLDate);
            } else {
                listAvailableRoom = RoomEJB.getAvailableRooms(session.getAttribute("UserLocationID").toString(), startSQLDate, endSQLDate);
            }

            if (listAvailableRoom.size() == 0) {
                result = "Sorry. There is no available room at the moment";
            } else {

                result = "{ \"aaData\":[";
                for (int i = 0; i < listAvailableRoom.size(); i++) {
                    Room room = (Room) listAvailableRoom.get(i);
                    String roomLocation = room.getRoomLocation();
                    Location location = LocationEJB.find(roomLocation);
                    String locationName = location.getLocationName();
                    /* JSON source sample
                     * { "aaData": [
                    ["Trident","Internet Explorer 4.0","Win 95+","4","X"],
                    ["Trident","Internet Explorer 5.0","Win 95+","5","C"],
                    ["Trident","Internet Explorer 5.5","Win 95+","5.5","A"],
                    ["Trident","Internet Explorer 6","Win 98+","6","A"]
                     * ]}
                     */
                    String jsonStringRoom =
                            "[\"" + room.getRoomId()
                            + "\",\"" + room.getRoomNumber()
                            + "\",\"" + room.getRoomType()
                            + "\",\"" + room.getRoomPricePerDay()
                            + "\",\"" + locationName
                            + "\"]";
                    result += jsonStringRoom + ",";
                }
                result = result.substring(0, result.length() - 1);
                result += "]}";
            }
        } finally {
            out.print(result);
            out.close();
        }
    }

    private void getAllRooms(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String result = "{ \"aaData\" : [";
        try {
            List<Room> roomList = RoomEJB.findAll();
            for (int i = 0; i < roomList.size(); i++) {
                Room room = (Room) roomList.get(i);
                String jsonStringRoom =
                        "[\"" + room.getRoomId()
                        + "\",\"" + room.getRoomNumber()
                        + "\",\"" + room.getRoomType()
                        + "\",\"" + room.getRoomPricePerDay()
                        + "\",\"" + room.getRoomLocation() + "\"]";
                result += jsonStringRoom + ",";
            }
            result = result.substring(0, result.length() - 1);
            result += "]}";
            out.print(result);
        } finally {
            out.close();
        }
    }

    public void getCustomer(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, NullPointerException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            String customerID = request.getParameter("CustomerID");
            Customer customer = CustomerEJB.find(customerID);
            if (customer != null) {
                out.write(customer.getCustomerName() + ",");
                out.write(customer.getCustomerPhone() + ",");
                out.write(customer.getCustomerEmail());
            } else {
                out.write("NewCustomer");
            }
        } catch (Exception ex) {
            Logger.getLogger(controller.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void bookRooms(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        HttpSession session = request.getSession();
        String strRoomID = request.getParameter("roomID");
        String bookStatus = "Booking successful";
        String bookMessage = null;
        if (strRoomID == null) {
            bookStatus = "Booking unsucessful. Please choose a room";
        } else {
            String customerID = request.getParameter("customerID");
            String customerName = request.getParameter("customerName");
            String customerEmail = request.getParameter("customerEmail");
            String customerPhone = request.getParameter("customerPhone");
            if (request.getParameter("NewCustomer").equals("true")) {
                Customer customer = new Customer();
                customer.setCustomerId(customerID);
                customer.setCustomerName(customerName);
                customer.setCustomerEmail(customerEmail);
                customer.setCustomerPhone(customerPhone);
                CustomerEJB.create(customer);
            }

            String startDate = request.getParameter("checkinDate");
            String endDate = request.getParameter("checkoutDate");
            Date arrivalDate = Date.valueOf(startDate);
            Date departureDate = Date.valueOf(endDate);
            Short roomID = Short.parseShort(strRoomID);
            String userID = session.getAttribute("UserId").toString();

            Booking booking = new Booking();
            booking.setRoomId(roomID);
            booking.setUserId(userID);
            booking.setCustomerId(customerID);
            booking.setDepartureDate(departureDate);
            booking.setArrivalDate(arrivalDate);
            BookingEJB.create(booking);
            System.out.println(booking.getCustomerId());
            System.out.println(booking.getUserId());
        }
        out.write(bookStatus);
    }

    public void getBookedRoom(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        HttpSession session = request.getSession();
        String result = null;
        List<Booking> listBookedRoom = new ArrayList<Booking>();
        if (((String) session.getAttribute("UserRole")).equals("NationalManager")) {
            listBookedRoom = BookingEJB.findAll();
        } else {
            String locationID = session.getAttribute("UserLocationID").toString();
            listBookedRoom = BookingEJB.findLocalBook(locationID);
        }
        if (listBookedRoom.size() == 0) {
            result = "Sorry. There is no booked room at the moment";
        } else {

            result = "{ \"aaData\":[";
            for (int i = 0; i < listBookedRoom.size(); i++) {
                Booking bookingRecord = (Booking) listBookedRoom.get(i);
                Integer bookingID = bookingRecord.getBookingId();
                String userID = bookingRecord.getUserId();
                String customerID = bookingRecord.getCustomerId();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                String arrivalDate = sdf.format(bookingRecord.getArrivalDate());
                System.out.println(arrivalDate);
                String departureDate = sdf.format(bookingRecord.getDepartureDate());
                System.out.println(departureDate);
                //get Room info
                Room room = RoomEJB.find(bookingRecord.getRoomId());
                Integer roomNumber = room.getRoomNumber();
                String roomLocationName = LocationEJB.find(room.getRoomLocation()).getLocationName();
                //get Customer info
                String customerName = CustomerEJB.find(bookingRecord.getCustomerId()).getCustomerName();

                /* JSON source sample
                 * { "aaData": [
                ["Trident","Internet Explorer 4.0","Win 95+","4","X"],
                ["Trident","Internet Explorer 5.0","Win 95+","5","C"],
                ["Trident","Internet Explorer 5.5","Win 95+","5.5","A"],
                ["Trident","Internet Explorer 6","Win 98+","6","A"]
                 * ]}
                 */
                String jsonStringBook =
                        "[\"" + bookingID
                        + "\",\"" + customerName
                        + "\",\"" + roomNumber
                        + "\",\"" + roomLocationName
                        + "\",\"" + arrivalDate
                        + "\",\"" + departureDate
                        + "\",\"" + userID
                        + "\",\"" + bookingID
                        + "\"]";
                result += jsonStringBook + ",";
            }
            result = result.substring(0, result.length() - 1);
            result += "]}";
        }
        out.print(result);
        out.close();
    }

    public void deleteBookedRoom(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String bookStatus = "Delete successfully";
        String bookingID = request.getParameter("bookingID");
        Booking booking = BookingEJB.find(Integer.parseInt(bookingID));
        BookingEJB.remove(booking);
        out.write(bookStatus);
    }

    public void getAllItems(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String result = "{ \"aaData\" : [";
        try {
            List<Item> itemList = new ArrayList<Item>();
            if (((String) session.getAttribute("UserRole")).equals("NationalManager")) {
                itemList = ItemEJB.findAll();
            } else {
                String locationID = session.getAttribute("UserLocationID").toString();
                itemList = ItemEJB.findLocalItem(locationID);
            }
            if (itemList.size() == 0) {
                result = "Sorry. Everything is sold out.";
            } else {
                for (int i = 0; i < itemList.size(); i++) {
                    Item item = (Item) itemList.get(i);
                    String itemLocation = item.getItemLocation();
                    Location location = LocationEJB.find(itemLocation);
                    String locationName = location.getLocationName();
                    String jsonStringRoom =
                            "[\"" + item.getItemId()
                            + "\",\"" + item.getItemName()
                            + "\",\"" + item.getItemOriginalPrice()
                            + "\",\"" + item.getItemSellingPrice()
                            + "\",\"" + item.getItemQuantity()
                            + "\",\"" + item.getItemType()
                            + "\",\"" + locationName + "\"]";
                    result += jsonStringRoom + ",";
                }
                result = result.substring(0, result.length() - 1) + "]}";
            }
            out.print(result);
        } finally {

            out.close();
        }

    }

    public void sellItems(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        HttpSession session = request.getSession();
        String strItemID = request.getParameter("itemID");
        String sellStatus = "Selling successfully";
        String sellMessage = null;

        if (strItemID == null) {
            sellStatus = "Selling unsucessfully. Please choose a item";
        } else {
            String customerID = request.getParameter("customerID");
            String customerName = request.getParameter("customerName");
            String customerEmail = request.getParameter("customerEmail");
            String customerPhone = request.getParameter("customerPhone");
            String userID = session.getAttribute("UserId").toString();
            Integer tranQuantity = Integer.parseInt(request.getParameter("quantity"));
            Integer itemID = Integer.parseInt(strItemID);
            //Get transaction date
            java.util.Date today = new java.util.Date();
            java.sql.Date tranDate = new java.sql.Date(today.getTime());
            //Get total price
            Item item = ItemEJB.find(itemID);
            Double itemPrice = item.getItemSellingPrice();
            Double tranPrice = itemPrice * tranQuantity;
            //Get transaction location
            String tranLocation = item.getItemLocation();
            //Get current item quantity
            Integer itemQuantity = item.getItemQuantity();
            if (itemQuantity < tranQuantity) {
                sellStatus = "Selling unsucessfully. This item is sold out.";
            } else {
                item.setItemQuantity(itemQuantity - tranQuantity);
                if (request.getParameter("NewCustomer").equals("true")) {
                    Customer customer = new Customer();
                    customer.setCustomerId(customerID);
                    customer.setCustomerName(customerName);
                    customer.setCustomerEmail(customerEmail);
                    customer.setCustomerPhone(customerPhone);
                    CustomerEJB.create(customer);
                }
                Transaction tran = new Transaction();
                tran.setTransactionQuantity(tranQuantity);
                tran.setTransactionTotalPrice(tranPrice);
                tran.setItemId(itemID);
                tran.setCustomerId(customerID);
                tran.setUserId(userID);
                tran.setTransactionDate(today);
                tran.setTransactionLocation(tranLocation);
                TransactionEJB.create(tran);
            }

        }
        out.write(sellStatus);
    }

    public void getTransactions(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        HttpSession session = request.getSession();
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String result = "{ \"aaData\" : [";
        List<Transaction> transactionList = new ArrayList<Transaction>();
        try {
            if (((String) session.getAttribute("UserRole")).equals("NationalManager")) {
                transactionList = TransactionEJB.findAll();
            } else {
                String locationID = session.getAttribute("UserLocationID").toString();
                transactionList = TransactionEJB.findLocalTransaction(locationID);
            }
            for (int i = 0; i < transactionList.size(); i++) {
                Transaction transaction = (Transaction) transactionList.get(i);
                //Get transaction location
                String transactionLoc = transaction.getTransactionLocation();
                Location location = LocationEJB.find(transactionLoc);
                String locationName = location.getLocationName();
                //Get item name
                Integer itemID = transaction.getItemId();
                Item item = ItemEJB.find(itemID);
                String itemName = item.getItemName();
                //return JSON string
                String jsonStringRoom =
                        "[\"" + transaction.getTransactionId()
                        + "\",\"" + itemName
                        + "\",\"" + transaction.getTransactionQuantity()
                        + "\",\"" + transaction.getTransactionTotalPrice()
                        + "\",\"" + locationName
                        + "\",\"" + transaction.getCustomerId()
                        + "\",\"" + transaction.getUserId()
                        + "\",\"" + transaction.getTransactionId() + "\"]";
                result += jsonStringRoom + ",";
            }
            result = result.substring(0, result.length() - 1) + "]}";
            out.print(result);
        } finally {
            out.close();
        }

    }

    public void deleteTransaction(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        String status = "Delete successfully";
        String transactionID = request.getParameter("transactionID");
        Transaction transaction = TransactionEJB.find(Integer.parseInt(transactionID));
        TransactionEJB.remove(transaction);
        out.write(status);
    }

    private void createBookingReport(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        HttpSession session = request.getSession();
        String status = null;
        String reportFilePath = (String.valueOf(session.getAttribute("aPath"))).replace("/", "//");
        reportFilePath += "jasperreport//";
        String reportFileName = null;
        String arrivalDate = request.getParameter("ArrivalDate");
        String departureDate = request.getParameter("DepartureDate");
        //Date arrivalDate = Date.valueOf(startDate);
        //Date departureDate = Date.valueOf(endDate);
        Map parameters = new HashMap();
        //find Location
        String userRole = session.getAttribute("UserRole").toString();
        String locationID = null;
        String locationName = null;
        if (userRole.equals("NationalManager")) {
            locationName = request.getParameter("Location");
            if (locationName.equals("All")) {
                reportFileName = "national_booking_report";
                parameters.put("StartDate", arrivalDate);
                parameters.put("EndDate", departureDate);
                parameters.put("LocationName", "National");
                parameters.put("ImagePath", reportFilePath);
                parameters.put("UserID", session.getAttribute("UserId"));
                createReport(parameters, reportFilePath, reportFileName);
                status = "Done! Choose HTML or PDF<br/><br/>"
                        + "<a href=\"./jasperreport/national_booking_report.html\">"
                        + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                        + "<a href=\"./jasperreport/national_booking_report.pdf\">"
                        + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
            } else {
                if (locationName.equals("Sai Gon")) {
                    locationID = "1";
                } else if (locationName.equals("Da Nang")) {
                    locationID = "2";
                } else if (locationName.equals("Ha Noi")) {
                    locationID = "3";
                }
                Location loc = LocationEJB.find(locationID);
                locationName = loc.getLocationName();
                reportFileName = "local_booking_report";
                parameters.put("StartDate", arrivalDate);
                parameters.put("EndDate", departureDate);
                parameters.put("LocationID", locationID);
                parameters.put("LocationName", locationName);
                parameters.put("ImagePath", reportFilePath);
                parameters.put("UserID", session.getAttribute("UserId"));
                createReport(parameters, reportFilePath, reportFileName);
                status = "Done! Choose HTML or PDF<br/><br/>"
                        + "<a href=\"./jasperreport/local_booking_report.html\">"
                        + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                        + "<a href=\"./jasperreport/local_booking_report.pdf\">"
                        + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
            }

        } else {
            locationID = session.getAttribute("UserLocationID").toString();
            Location loc = LocationEJB.find(locationID);
            locationName = loc.getLocationName();
            reportFileName = "local_booking_report";
            parameters.put("StartDate", arrivalDate);
            parameters.put("EndDate", departureDate);
            parameters.put("LocationID", locationID);
            parameters.put("LocationName", locationName);
            parameters.put("ImagePath", reportFilePath);
            parameters.put("UserID", session.getAttribute("UserId"));
            createReport(parameters, reportFilePath, reportFileName);
            status = "Done! Choose HTML or PDF<br/><br/>"
                    + "<a href=\"./jasperreport/local_booking_report.html\">"
                    + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                    + "<a href=\"./jasperreport/local_booking_report.pdf\">"
                    + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
        }
        out.write(status);
    }

    private void createSalesReport(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        HttpSession session = request.getSession();
        String status = null;
        String reportFilePath = (String.valueOf(session.getAttribute("aPath"))).replace("/", "//");
        reportFilePath += "jasperreport//";
        String reportFileName = null;
        String arrivalDate = request.getParameter("ArrivalDate");
        String departureDate = request.getParameter("DepartureDate");
        //Date arrivalDate = Date.valueOf(startDate);
        //Date departureDate = Date.valueOf(endDate);
        Map parameters = new HashMap();
        //find Location
        String userRole = session.getAttribute("UserRole").toString();
        String locationID = null;
        String locationName = null;
        if (userRole.equals("NationalManager")) {
            locationName = request.getParameter("Location");
            if (locationName.equals("All")) {
                reportFileName = "national_transaction_report";
                parameters.put("StartDate", arrivalDate);
                parameters.put("EndDate", departureDate);
                parameters.put("LocationName", "National");
                parameters.put("ImagePath", reportFilePath);
                parameters.put("UserID", session.getAttribute("UserId"));
                createReport(parameters, reportFilePath, reportFileName);
                status = "Done! Choose HTML or PDF<br/><br/>"
                        + "<a href=\"./jasperreport/national_transaction_report.html\">"
                        + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                        + "<a href=\"./jasperreport/national_transaction_report.pdf\">"
                        + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
            } else {
                if (locationName.equals("Sai Gon")) {
                    locationID = "1";
                } else if (locationName.equals("Da Nang")) {
                    locationID = "2";
                } else if (locationName.equals("Ha Noi")) {
                    locationID = "3";
                }
                Location loc = LocationEJB.find(locationID);
                locationName = loc.getLocationName();
                reportFileName = "local_transaction_report";
                parameters.put("StartDate", arrivalDate);
                parameters.put("EndDate", departureDate);
                parameters.put("LocationID", locationID);
                parameters.put("LocationName", locationName);
                parameters.put("ImagePath", reportFilePath);
                parameters.put("UserID", session.getAttribute("UserId"));
                createReport(parameters, reportFilePath, reportFileName);
                status = "Done! Choose HTML or PDF<br/><br/>"
                        + "<a href=\"./jasperreport/local_transaction_report.html\">"
                        + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                        + "<a href=\"./jasperreport/local_transaction_report.pdf\">"
                        + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
            }

        } else {
            locationID = session.getAttribute("UserLocationID").toString();
            Location loc = LocationEJB.find(locationID);
            locationName = loc.getLocationName();
            reportFileName = "local_transaction_report";
            parameters.put("StartDate", arrivalDate);
            parameters.put("EndDate", departureDate);
            parameters.put("LocationID", locationID);
            parameters.put("LocationName", locationName);
            parameters.put("ImagePath", reportFilePath);
            parameters.put("UserID", session.getAttribute("UserId"));
            createReport(parameters, reportFilePath, reportFileName);
            status = "Done! Choose HTML or PDF<br/><br/>"
                    + "<a href=\"./jasperreport/local_transaction_report.html\">"
                    + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                    + "<a href=\"./jasperreport/local_transaction_report.pdf\">"
                    + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
        }
        out.write(status);
    }


    private void createUserStatistics(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        HttpSession session = request.getSession();
        String status = null;
        String reportFileName = null;
        String locationID = null;
        String locationName = null;

        String reportFilePath = (String.valueOf(session.getAttribute("aPath"))).replace("/", "//");
        reportFilePath += "jasperreport//";
        String arrivalDate = request.getParameter("ArrivalDate");
        String departureDate = request.getParameter("DepartureDate");
        
        //Date arrivalDate = Date.valueOf(startDate);
        //Date departureDate = Date.valueOf(endDate);
        Map parameters = new HashMap();
        //find Location
        String userRole = session.getAttribute("UserRole").toString();
        
        if (userRole.equals("NationalManager")) {
            locationName = request.getParameter("Location");
            if (locationName.equals("All")) {
                String employeeID = request.getParameter("EmployeeID");
                if (UserEJB.find(employeeID) != null) {
                    reportFileName = "national_user_statistics";
                    parameters.put("StartDate", arrivalDate);
                    parameters.put("EndDate", departureDate);
                    parameters.put("LocationName", "National");
                    parameters.put("ImagePath", reportFilePath);
                    parameters.put("UserID", session.getAttribute("UserId"));
                    parameters.put("EmployeeID", employeeID);
                    createReport(parameters, reportFilePath, reportFileName);
                    status = "Done! Choose HTML or PDF<br/><br/>"
                            + "<a href=\"./jasperreport/national_user_statistics.html\">"
                            + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                            + "<a href=\"./jasperreport/national_user_statistics.pdf\">"
                            + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
                } else {
                    status = "Sorry. This employeeID does not exist";
                }
                
            } else {
                if (locationName.equals("Sai Gon")) {
                    locationID = "1";
                } else if (locationName.equals("Da Nang")) {
                    locationID = "2";
                } else if (locationName.equals("Ha Noi")) {
                    locationID = "3";
                }
                String employeeID = request.getParameter("EmployeeID");
                if (!(request.getParameter("EmployeeID").equals(null)) && (UserEJB.findLocalUser(employeeID, locationID) != null)) {
                    Location loc = LocationEJB.find(locationID);
                    locationName = loc.getLocationName();
                    reportFileName = "local_user_statistics";
                    parameters.put("StartDate", arrivalDate);
                    parameters.put("EndDate", departureDate);
                    parameters.put("LocationID", locationID);
                    parameters.put("LocationName", locationName);
                    parameters.put("ImagePath", reportFilePath);
                    parameters.put("EmployeeID", employeeID);
                    parameters.put("UserID", session.getAttribute("UserId"));
                    createReport(parameters, reportFilePath, reportFileName);
                    status = "Done! Choose HTML or PDF<br/><br/>"
                            + "<a href=\"./jasperreport/local_user_statistics.html\">"
                            + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                            + "<a href=\"./jasperreport/local_user_statistics.pdf\">"
                            + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
                } else {
                    status = "Sorry. This employeeID does not exist";
                }
            }
        } else {
            String employeeID = request.getParameter("EmployeeID");
            locationID = session.getAttribute("UserLocationID").toString();
             if ((!request.getParameter("EmployeeID").equals(null)) && (UserEJB.findLocalUser(employeeID, locationID) != null)) {
                Location loc = LocationEJB.find(locationID);
                locationName = loc.getLocationName();
                reportFileName = "local_user_statistics";
                parameters.put("StartDate", arrivalDate);
                parameters.put("EndDate", departureDate);
                parameters.put("LocationID", locationID);
                parameters.put("LocationName", locationName);
                parameters.put("EmployeeID", employeeID);
                parameters.put("ImagePath", reportFilePath);
                parameters.put("UserID", session.getAttribute("UserId"));
                createReport(parameters, reportFilePath, reportFileName);
                status = "Done! Choose HTML or PDF<br/><br/>"
                        + "<a href=\"./jasperreport/local_user_statistics.html\">"
                        + "<img src=\"images/html.png\" alt=\"HTML\" width=\"48\" height=\"48\" /></a>"
                        + "<a href=\"./jasperreport/local_user_statistics.pdf\">"
                        + "<img src=\"images/pdf.png\" alt=\"PDF\" width=\"48\" height=\"48\" /></a> ";
             } else {
                status = "Sorry. This employeeID does not exist";
             }
        }
        out.write(status);
    }

    public void createReport(Map parameters, String reportFilePath, String reportFileName) {
        InputStream inputFile = null;
        try {
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/bumblebee_rdis", "rdis_user", "qwerty");
            String reportFile = reportFilePath + reportFileName + ".jrxml";
            String htmlFile = reportFilePath + reportFileName + ".html";
            inputFile = new FileInputStream(new File(reportFile));
            JasperDesign jasperDesign = JRXmlLoader.load(inputFile);
            JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
            JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, connection);
            JRPdfExporter pdfExporter = new JRPdfExporter();
            pdfExporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
            pdfExporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, reportFilePath + reportFileName + ".pdf");
            System.out.println("Exporting report...");
            pdfExporter.exportReport();
            System.out.println("Done!");
            JasperExportManager.exportReportToHtmlFile(jasperPrint, htmlFile);
        } catch (Exception ex) {
            Logger.getLogger(controller.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                inputFile.close();
                //outputFile.close();
            } catch (IOException ex) {
                Logger.getLogger(controller.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }
}
